/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package aviso.property.facade;

import aviso.property.cache.Cache;
import aviso.property.entity.Item;
import aviso.property.entity.ItemCategory;
import aviso.property.entity.Property;
import aviso.property.entity.Property.PropertyRangeType;
import aviso.property.entity.Property.PropertyType;
import aviso.property.entity.PropertyGroup;
import aviso.property.entity.PropertyValue;
import aviso.property.helpers.PropertyValueCountBase;
import aviso.property.helpers.PropertyValuePair;
import aviso.property.helpers.PropertyValuePairs;
import aviso.property.helpers.PropertyValueRange;
import aviso.property.helpers.PropertyValues;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.annotation.Resource;
import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.ejb.LocalBean;
import javax.ejb.SessionContext;
import javax.ejb.TransactionAttribute;
import javax.ejb.TransactionAttributeType;
import javax.persistence.EntityManager;
import javax.persistence.FlushModeType;
import javax.persistence.LockModeType;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.sql.DataSource;
import org.apache.commons.lang3.ArrayUtils;

/**
 *
 * @author avgusti
 */
@Stateless
@LocalBean
public class PropertiesQueryFacade {

    //@Resource(mappedName = "jdbc/__default")
    @Resource(mappedName = "jdbc/_aviso")
    private DataSource ds;
    @PersistenceContext(unitName = "aviso")
    private EntityManager em;
    @EJB
    ItemFacade itemFacade;

    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void loadCache() {

        Cache cache = Cache.getInstance();
        cache.setItemCategories(em.createQuery("select c from ItemCategory c", ItemCategory.class).getResultList());
        cache.setProperties(em.createQuery("select p from Property p", Property.class).getResultList());
        cache.setPropertyGroups(em.createQuery("select g from PropertyGroup g", PropertyGroup.class).getResultList());
        //load propety valuse counts

        List<PropertyValueCountBase> pvps = new ArrayList(1000);// countPropeties(cache.getProperties());
        for (Property p : cache.getProperties()) {
            if (p.getRange() == PropertyRangeType.Between) {
                pvps.add(getPropetyValuesInfo(p));
                continue;
            }
            if (p.getRange() == PropertyRangeType.None) {
                continue;
            }
            if (p.getType() == PropertyType.Text) {
                continue;
            }
            List<PropertyValuePair> pairs = countPropety(p);
            if (pairs.isEmpty()) {
                continue;
            }
            int rcount = p.getRange().getCount();
            if (rcount > 0) // merge pairs
            {
                List<PropertyValueRange> tp = new ArrayList<PropertyValueRange>(rcount + 1);
                long partLen = pairs.size() / rcount;
                partLen = partLen > 0 ? partLen : 1;
                int c = 0, s = pairs.size();
                String startVal = pairs.get(c).getStringValue(), endVal;
                do {

                    c += partLen;
                    endVal = pairs.get(c >= s ? s - 1 : c).getStringValue();
                    PropertyValuePair min = new PropertyValuePair(p, startVal);
                    PropertyValuePair max = new PropertyValuePair(p, endVal);
                    PropertyValueRange pvr = new PropertyValueRange(min, max);
                    tp.add(pvr);
                    startVal = endVal;
                } while (c < s);
                pvps.addAll(tp);
            } else {
                pvps.addAll(pairs);
            }
        }

        Property tp = null;
        PropertyValuePairs pairs = null;
        int counter = 0;
        for (PropertyValueCountBase pair : pvps) {
            if (tp == null || tp != pair.getProperty()) {
                tp = pair.getProperty();
                pairs = new PropertyValuePairs(tp);
                cache.getIdsOfPropertyValuePairs().put(pair.getProperty().getId(), pairs);
            }
            counter++;
            if (counter == 1000) {
                System.gc();
                Logger.getAnonymousLogger().log(Level.INFO, "{0}", counter);
                counter = 0;
            }
            long[] ids = {};
            if (pair.getProperty().getRange().getCount() > 0) {
                PropertyValueRange range = (PropertyValueRange) pair;
                ids = loadPropertyValueRangeItemIds(pair.getProperty().getId(), range.getMin().getStringValue(), range.getMax().getStringValue());
            } else {
                ids = loadPropertyValueItemIds(pair.getProperty().getId(), pair.getValueForCompare());
            }
            if (ids.length > 0) {
                pair.setValueIDs(ids);
            }
            if (pair.getValueForCompare() != null) {
                pairs.getVpvp().put(pair.getValueForCompare(), pair);
            }
        }
    }

    public long[] loadPropertyValueItemIds(long propID, String value) {
        long[] ids = new long[0];
        int ids_count = 0;
        Connection con = null;
        try {
            con = ds.getConnection();
            PreparedStatement stm = con.prepareStatement("select count(*) from propertyvalue v where v.property_id=? and v.stringvalue=?");
            stm.setLong(1, propID);
            stm.setString(2, value);
            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                System.out.println("COUNT(*)=" + rs.getInt(1));
                ids_count = rs.getInt(1);
            }
            ids = new long[ids_count];
            // close ResultSet rs
            rs.close();
            stm = con.prepareStatement("select v.item_id from propertyvalue v where v.property_id=? and v.stringvalue=? order by v.item_id");
            stm.setLong(1, propID);
            stm.setString(2, value);
            rs = stm.executeQuery();

            int i = 0;
            while (rs.next()) {
                long l = rs.getLong(1);
                ids[i] = l;
                i++;
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(PropertiesQueryFacade.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(PropertiesQueryFacade.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return ids;
    }

    private long[] loadPropertyValueRangeItemIds(long propID, String min, String max) {
        long[] ids = new long[0];
        int ids_count = 0;
        Connection con = null;
        try {
            con = ds.getConnection();
            PreparedStatement stm = con.prepareStatement("select count(*) from propertyvalue v where v.property_id=? and v.stringvalue between ? and ? ");
            stm.setLong(1, propID);
            stm.setString(2, min);
            stm.setString(3, max);
            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                System.out.println("COUNT(*)=" + rs.getInt(1));
                ids_count = rs.getInt(1);
            }
            ids = new long[ids_count];
            // close ResultSet rs
            rs.close();
            stm = con.prepareStatement("select v.item_id from propertyvalue v where v.property_id=? and v.stringvalue between ? and ? order by v.item_id");
            stm.setLong(1, propID);
            stm.setString(2, min);
            stm.setString(3, max);
            rs = stm.executeQuery();

            int i = 0;
            while (rs.next()) {
                long l = rs.getLong(1);
                ids[i] = l;
                i++;
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(PropertiesQueryFacade.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(PropertiesQueryFacade.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return ids;
    }

    public List<Property> listPropertiesForCategory(ItemCategory ic) {
        List<Property> res = new ArrayList<Property>();
        ItemCategory ict = ic;
        while (ict != null && ict.getId() != null) {
            res.addAll(em.createQuery("select p from Property p where p.category=:category order by p.group.id", Property.class).setParameter("category", ict).getResultList());
            ict = ict.getParent();
        }
        return res;
    }

    // max,min,count
    public PropertyValues getPropetyValuesInfo(Property p) {
        PropertyValues res = null;
        if (p != null) {
            res = em.createQuery("select new aviso.property.helpers.PropertyValues(v.property,min(v.stringvalue),max(v.stringvalue),count(v.stringvalue)) "
                    + "from PropertyValue v where v.property =:property", PropertyValues.class).setParameter("property", p).getSingleResult();
        }
        return res;
    }

    public List<PropertyValuePair> countPropety(Property p) {
        List res = null;
        if (p != null) {
            res = em.createQuery("select new aviso.property.helpers.PropertyValuePair(v.property,v.stringvalue,count(v.stringvalue)) from PropertyValue v where v.property =:property group by v.property.id, v.property.name, v.stringvalue order by v.stringvalue", PropertyValuePair.class).setParameter("property", p).getResultList();
        }
        return res;
    }

    public List<PropertyValuePair> countPropeties(List<Property> ps) {
        List res = null;
        if (ps.size() > 0) {
            res = em.createQuery("select new aviso.property.helpers.PropertyValuePair(v.property,v.stringvalue,count(v.stringvalue)) from PropertyValue v where v.property IN :properties group by v.property.id, v.property.name, v.stringvalue, v.property.type", PropertyValuePair.class).setParameter("properties", ps).getResultList();
        }
        return res;
    }

    public boolean saveProperties(int i, int page, long id) {
        Logger.getAnonymousLogger().log(Level.INFO, "{0}", i);
        Query q = em.createQuery("select v from PropertyValue v where v.property.id=:id", PropertyValue.class).setParameter("id", id).setMaxResults(page).setFirstResult(i * page);
        List<PropertyValue> pvs;
        pvs = q.getResultList();

        for (PropertyValue v : pvs) {
            v.setStringvalue(v.getStringvalue());
            em.merge(v);
        }
        em.flush();
        em.clear();
        return pvs.size() > 0;
    }

    public List<Item> listItems(ItemCategory ic, List<PropertyValueCountBase> pvc) {
        return listItems(ic, pvc, 0, 20);
    }

    public List<Item> listItems(ItemCategory ic, List<PropertyValueCountBase> pvc, int offset, int perPage) {
        List<Item> items = new ArrayList<Item>();
        boolean useFilter = false;
        long[] ids = {};
        if (pvc != null && pvc.size() > 0) {
            useFilter = true;
            ids = pvc.get(0).getValueIDs();
            int i = 1;
            Property p = pvc.get(0).getProperty();
            while (i < pvc.size()) {
                if (pvc.get(i).getProperty() == p) {//merge
                    long[] vals = pvc.get(i).getValueIDs();
                    long[] tmp = new long[ids.length + vals.length];
                    for (int k = 0; k < ids.length; k++) {
                        tmp[k] = ids[k];
                    }
                    for (int k = 0; k < vals.length; k++) {
                        tmp[k + ids.length] = vals[k];
                    }
                    ids = tmp;
                } else {
                    //filter
                    long[] vals = pvc.get(i).getValueIDs();
                    Arrays.sort(vals);
                    Arrays.sort(ids);
                    int vpos = 0;
                    int count = 0;
                    for (int k = 0; k < ids.length; k++) {
                        while (vpos < vals.length && vals[vpos] < ids[k]) {
                            vpos++;
                        }
                        if (vpos < vals.length) {
                            if (vals[vpos] == ids[k]) {
                                vpos++;
                                count++;
                            } else {
                                ids[k] = 0;
                            }
                        } else {
                            ids[k] = 0;
                        }
                    }
                    long[] tmp = new long[count];
                    vpos = 0;
                    for (int k = 0; k < ids.length; k++) {
                        if (ids[k] != 0) {
                            if (vpos < tmp.length) {
                                tmp[vpos] = ids[k];
                                vpos++;
                            } else {
                                tmp[vpos - 1] = ids[k];
                            }
                        }
                    }
                    ids = tmp;
                    p = pvc.get(i).getProperty();

                }
                i++;
            }
        }
        Query q;

        if (!useFilter) {
            q = em.createQuery("select i from Item", Item.class);
        } else {
            List<Long> lids = new ArrayList<Long>();
            lids = Arrays.asList(ArrayUtils.toObject(Arrays.copyOfRange(ids, offset, perPage)));
            //lids=ArrayUtils. Arrays.asList(ids);
            q = em.createQuery("select i from Item i where i.id in :ids", Item.class).setParameter("ids", lids);
        }

        q.setFirstResult(offset).setMaxResults(perPage);


        return q.getResultList();
//        if (pvs.size() > 0) {
//            List<Long> itemids = new ArrayList<Long>();
//            Collections.sort(pvs);
//            List<Long> titemids;
//            List<Long> totitemids = new ArrayList<Long>();
//            int i = 0;
//            while (i < pvs.size()) {
//                PropertyValuePair pvp = pvs.get(i);
//                Long tpropid = pvp.getProperty().getId();
//                totitemids.clear();
//                while (tpropid == pvp.getProperty().getId()) {
//                    titemids = itemids.size() > 0 ? em.createQuery("select v.item.id from PropertyValue v where v.stringvalue=:val and v.property.id=:propid and v.item.id in :ids", Long.class).setParameter("val", pvp.getStringValue()).setParameter("propid", pvp.getProperty().getId()).setParameter("ids", itemids).getResultList()
//                            : em.createQuery("select v.item.id from PropertyValue v where v.stringvalue=:val and v.property.id=:propid", Long.class).setParameter("val", pvp.getStringValue()).setParameter("propid", pvp.getProperty().getId()).getResultList();
//                    totitemids.addAll(titemids);
//                    i++;
//                    if (i < pvs.size()) {
//                        pvp = pvs.get(i);
//                    } else {
//                        break;
//                    }
//                }
//                itemids.clear();
//                itemids.addAll(totitemids);
//            }
//            if (itemids.size() > 0) {
//                itemids = em.createQuery("select i.id from Item i where i.category=:category and i.id in :ids", Long.class).setParameter("category", ic).setParameter("ids", itemids).getResultList();
//            }
//            if (itemids.size() > 0) {
//                items = em.createQuery("select i from Item i where i.id in :ids", Item.class).setParameter("ids", itemids).getResultList();
//            }
//        }
    }
}
